#include <sqlite3.h>
#include <stdio.h>
#include <string.h>
#include <stdbool.h>
#include <stdlib.h>
#include "StdSqlite.h"
#include "DoubleLinkList.h"

#define false 0
#define true 1

struct USER
{
    char Name[30];
    char account[30];
    char passwd[30];
};
typedef struct USER User;

struct StdSqlite
{
    sqlite3 *db;
};

SQL *InitSqlite(const char *filename)
{
    SQL *s = (SQL *)malloc(sizeof(SQL));
    if (s == NULL)
    {
        printf("initSqlite malloc error\n");
        return NULL;
    }

    if (sqlite3_open(filename, &s->db) != SQLITE_OK)
    {
        printf("open %s error:%s", filename, sqlite3_errmsg(s->db));
        free(s);
        return NULL;
    }
    return s;
}

void CreateTable(SQL *s, const char *tableName, char **prolist, int row)
{
    char property[1024] = {0};
    for (int i = 0; i < row; i++)
    {
        strcat(property, prolist[i * 2]);
        strcat(property, " ");
        strcat(property, prolist[i * 2 + 1]);
        if (i != row - 1)
        {
            strcat(property, ",");
        }
    }
    char sql[4096] = {0};
    sprintf(sql, "create table %s(%s);", tableName, property);
    if (sqlite3_exec(s->db, sql, NULL, NULL, NULL) != SQLITE_OK)
    {
        printf("create table error: %s\n", sqlite3_errmsg(s->db));
    }
}

void DeleteTable(SQL *s, const char *tableName)
{
    char sql[4096] = {0};
    sprintf(sql, "drop table %s;", tableName);
    if (sqlite3_exec(s->db, sql, NULL, NULL, NULL) != SQLITE_OK)
    {
        printf("drop table error: %s\n", sqlite3_errmsg(s->db));
    }
}

void ClearDataTable(SQL *s, const char *tableName)
{
    char sql[4096] = {0};
    sprintf(sql, "delete from %s;", tableName);
    if (sqlite3_exec(s->db, sql, NULL, NULL, NULL) != SQLITE_OK)
    {
        printf("delete table error: %s\n", sqlite3_errmsg(s->db));
    }
}

void InsertData(SQL *s, const char *tableName, char **values, int size)
{
    char sql[4096] = {0};
    strcat(sql, "INSERT INTO ");
    strcat(sql, tableName);
    strcat(sql, " VALUES(");

    for (int i = 0; i < size; i++)
    {
        strcat(sql, "?");
        if (i != size - 1)
        {
            strcat(sql, ",");
        }
    }

    strcat(sql, ");");

    sqlite3_stmt *stmt;
    if (sqlite3_prepare_v2(s->db, sql, -1, &stmt, NULL) == SQLITE_OK)
    {
        for (int i = 0; i < size; i++)
        {
            sqlite3_bind_text(stmt, i + 1, values[i], -1, SQLITE_TRANSIENT);
        }
        if (sqlite3_step(stmt) != SQLITE_DONE)
        {
            printf("Insert data error: %s\n", sqlite3_errmsg(s->db));
        }
        sqlite3_finalize(stmt);
    }
    else
    {
        printf("Insert data error: %s\n", sqlite3_errmsg(s->db));
    }
}
void InsertData2(SQL *s, const char *tableName, char **values, int size)
{
    char valueList[1024];
    for (int i = 0; i < size; i++)
    {
        strcat(valueList, values[i]);
        if (i != size - 1)
        {
            strcat(valueList, ",");
        }
    }
    char sql[4096] = {0};
    sprintf(sql, "Insert into %s values(%s);", tableName, valueList);
    if (sqlite3_exec(s->db, sql, NULL, NULL, NULL) != SQLITE_OK)
    {
        printf("Insert data error: %s\n", sqlite3_errmsg(s->db));
    }
}
void DeleteData(SQL *s, const char *tableName, const char *where)
{
    char sql[4096] = {0};
    if (where == NULL)
    {
        sprintf(sql, "delete from %s;", tableName);
    }
    else
    {
        sprintf(sql, "delete from %s where %s", tableName, where);
    }
    // printf("sql = %s\n",sql);
    if (sqlite3_exec(s->db, sql, NULL, NULL, NULL) != SQLITE_OK)
    {
        printf("delete data error: %s\n", sqlite3_errmsg(s->db));
    }
}

void UpdateData(SQL *s, const char *tableName, const char *SetValue, const char *where)
{
    char sql[4096] = {0};
    sprintf(sql, "update %s set %s where %s", tableName, SetValue, where);

    if (sqlite3_exec(s->db, sql, NULL, NULL, NULL) != SQLITE_OK)
    {
        printf("update data error: %s\n", sqlite3_errmsg(s->db));
    }
}

void GetTableInfo(SQL *s, const char *tableName, char ***result, int *row, int *column)
{
    char sql[4096] = {0};
    sprintf(sql, "select * from %s", tableName);
    int rc = sqlite3_get_table(s->db, sql, result, row, column, NULL);
    if (rc != SQLITE_OK)
    {
        printf("GetTableInfo error: %s\n", sqlite3_errmsg(s->db));
    }
}

void SelectInfo(SQL *s, const char *sql, char ***result, int *row, int *column)
{
    if (sqlite3_get_table(s->db, sql, result, row, column, NULL) != SQLITE_OK)
    {
        printf("SelectInfo error:%s", sqlite3_errmsg(s->db));
    }
}

char *GetTableData(SQL *s, const char *tableName, char *account, char *passwd)
{
    char sql[4096] = {0};
    sprintf(sql, "select *from %s", tableName);
    char **result;
    int row, column;
    if (sqlite3_get_table(s->db, sql, &result, &row, &column, NULL) != SQLITE_OK)
    {
        printf("GetTableLen error:%s", sqlite3_errmsg(s->db));
    }
    for (int i = 1; i <= row; i++)
    {
        if (strcmp(result[i * column], account) == 0 && strcmp(result[i * column + 1], passwd) == 0)
        {
            return result[i * column + 2];
        }
    }
    sqlite3_free_table(result);
    return NULL;
}
char *GetTableData2(SQL *s, const char *tableName, char *account)
{
    char sql[4096] = {0};
    sprintf(sql, "select *from %s", tableName);
    char **result;
    int row, column;
    if (sqlite3_get_table(s->db, sql, &result, &row, &column, NULL) != SQLITE_OK)
    {
        printf("GetTableLen error:%s", sqlite3_errmsg(s->db));
    }

    for (int i = 1; i <= row; i++)
    {
        if (strcmp(result[i * column], account) == 0)
        {
            char *tmp = result[i * column + 2];
            return tmp;
        }
    }
    sqlite3_free_table(result);
    return NULL;
}

void PrintTableData(SQL *s, const char *tableName)
{
    char sql[4096] = {0};
    sprintf(sql, "select *from %s", tableName);
    char **result;
    int row, column;
    if (sqlite3_get_table(s->db, sql, &result, &row, &column, NULL) != SQLITE_OK)
    {
        printf("GetTableLen error:%s", sqlite3_errmsg(s->db));
    }
    for (int i = 0; i <= row; i++)
    {
        for (int j = 0; j < column; j++)
        {
            printf("%s|", result[i * column + j]);
        }
        printf("\n");
    }
    sqlite3_free_table(result);
}

void DeleteTableAccount(SQL *s, const char *tableName, char *account, char *passwd)
{
    char *tmp = GetTableData(s, tableName, account, passwd);
    if (tmp == NULL)
    {
        return;
        printf("没有该账号\n");
    }
    else
    {
        char sql[4096] = {0};
        sprintf(sql, "delete from %s where account = '%s'", tableName, account);
        if (sqlite3_exec(s->db, sql, NULL, NULL, NULL) != SQLITE_OK)
        {
            printf("update data error: %s\n", sqlite3_errmsg(s->db));
        }
        printf("删除成功\n");
    }
}

void DeleteOnlineData(SQL *s, const char *tableName, char *FromName)
{
    char sql[4096] = {0};
    sprintf(sql, "delete from %s where name = '%s'", tableName, FromName);
    if (sqlite3_exec(s->db, sql, NULL, NULL, NULL) != SQLITE_OK)
    {
        printf("update data error: %s\n", sqlite3_errmsg(s->db));
    }
}
int CheckTableExists(SQL *s, const char *tableName)
{
    char sql[4096] = {0};
    snprintf(sql, sizeof(sql), "SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name='%s';", tableName);

    sqlite3_stmt *stmt;
    if (sqlite3_prepare_v2(s->db, sql, -1, &stmt, NULL) == SQLITE_OK)
    {
        if (sqlite3_step(stmt) == SQLITE_ROW)
        {
            int rowCount = sqlite3_column_int(stmt, 0);
            sqlite3_finalize(stmt);
            return (rowCount > 0) ? 1 : 0; // 1 表示表格存在，0 表示表格不存在
        }
        sqlite3_finalize(stmt);
    }
    else
    {
        printf("query error: %s\n", sqlite3_errmsg(s->db));
    }

    return -1; // 查询发生错误
}

void FreeInfoReault(char **result)
{
    sqlite3_free_table(result);
}

int GetTableLen(SQL *s, const char *tableName)
{
    char sql[4096] = {0};
    sprintf(sql, "select count(*) from %s", tableName);

    char **result;
    int row, column;
    if (sqlite3_get_table(s->db, sql, &result, &row, &column, NULL) != SQLITE_OK)
    {
        printf("GetTableLen error:%s", sqlite3_errmsg(s->db));
    }
    int len = atoi(result[column]);
    sqlite3_free_table(result);
    return len;
}

int IsTableEmpty(SQL *s, const char *tableName)
{
    if (GetTableLen(s, tableName) > 0)
    {
        return false;
    }
    return true;
}

void FreeSqlite(SQL *s)
{
    if (s == NULL)
    {
        return;
    }
    sqlite3_close(s->db);
    free(s);
}
